#!/usr/bin/env python
# encoding: utf-8
#################
# Name:         一点接口用户数据，清洗 
# Purpose:
#
# Author:      stephen
#
# Created:     02/20/2019
# Licence:     1.0.0
#
#################
import logging
import ConfigParser
import re, os, random, sys, string, hashlib, math, uuid
import calendar
import json,struct
import numpy as np
import pymysql

#from datetime import *
import datetime
import time
from LOCAL_CONFIG import *
from phone   import Phone
from birth   import Birth
from pyimei  import ImeiSupport
from func    import  *  

reload(sys)
sys.setdefaultencoding('utf-8')
#sys.path.append(os.environ['HOME']+"/bin")
sys.path.append(os.environ['HOME']+"/lib")  
from pyctlib import *

VERSION_NUM  = '1.0.0'
VERSION_DATE = '2019-02-20'

class UniDB:
  def __init__(self,user,passwd,server,port,dbname,dbtype="Oracle"):
    self.__server = server
    self.__port   = port
    self.__dbname = dbname
    self.__user   = user
    self.__passwd = passwd
    self.__dbtype = dbtype.lower()
    self.dbconn   = None
  def __del__(self):
    pass
  def open(self):
    if self.__dbtype == "oracle":
      os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
      import cx_Oracle
      dsn_tns = cx_Oracle.makedsn(self.__server, self.__port  , self.__dbname)
      self.dbconn=cx_Oracle.connect(self.__user, self.__passwd, dsn_tns)
    elif self.__dbtype == "mysql":
      #os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
      import pymysql
      self.dbconn=pymysql.connect(host  =self.__server, port=self.__port, user=self.__user,\
                                  passwd=self.__passwd, db  =self.__dbname, charset='utf8')
    else:
      raise TypeError("OnlyOracle.")
    logging.info("DB[%s] Open."%self.__dbtype)
  def close(self):
    try:
      if self.dbconn:
        self.dbconn.close()
      self.dbconn=None
      logging.info("DB[%s]Closed."%self.__dbtype)
    except Exception,e:
      logging.info("DB[%s]ClosedExcept[%s]."%(self.__dbtype,e))
      self.dbconn=None
      
class UniCfg:
  def __init__(self,cfgfile):
    self.cfgfile=cfgfile
    self.load_cfg()
  def load_cfg(self):
    cf = ConfigParser.ConfigParser()
    cf.read(self.cfgfile)
    
    self.dbhost = cf.get("db"   , "dbhost")
    self.dbport = cf.getint("db", "dbport")
    self.dbname = cf.get("db"   , "dbname")
    self.dbuser = cf.get("db"   , "dbuser")
    self.dbpass = cf.get("db"   , "dbpass")
    self.dbtype = cf.get("db"   , "dbtype")
    
    self.logfile = cf.get("log","logfile")
    self.loglevel= cf.get("log","loglevel").upper()

class Handle:
  def __init__(self,udb=None,cfg=None):
    self.dbconn  = udb.dbconn
    self.cfg  = cfg
    self.__user_id     = 1
    self.__table       = 'usr_user_basic_info'
    self.__init_info() 
    self.__synonym_key = ['user_sex', 'cur_age', 'constellation','blood_type',\
         'profession', 'education_degree', 'cet46', 'residential_province', \
         'residential_city', 'residential_district', 'idcard_province',\
          'idcard_district']
     
  def __init_info(self):
    #sql = "select * from usr_user_basic_info where imei='355819409249602'"
    sql = " select imei from usr_user_basic_info  where imei is not null"
    self.__users = self.__select_db(sql, True) 

    #sql = "select * from yd_client_device where imei='355819409249602'" 
    sql = "select * from yd_client_device where imei is not null" 
    self.__imei = self.__select_db(sql, True)                              

    sql = "select tag_name from tg_tags_info where class_id=29"
    self.__province  = self.__select_db(sql, True)
    sql = "select tag_name from tg_tags_info where class_id=30"
    self.__city  = self.__select_db(sql, True)
    sql = "select tag_name from tg_tags_info where class_id=31"
    self.__district  = self.__select_db(sql, True)
    sql = "select tag_name from tg_tags_info where class_id=32"
    self.__community  = self.__select_db(sql, True)
    sql = "select province, city from yd_province_city_info" 
    self.__province_city = self.__select_db(sql, True)
     
 
  def handle_data (self):
    try:
      #self.__tidy_data( self.__users) 
      #self.__tidy_data( self.__imei )
      logging.info('program init finish!')
      self.__fetch_data()
    except  Exception,err:                                                   
      import traceback                                                       
      import StringIO                                                        
      fp = StringIO.StringIO()                                               
      traceback.print_exc(file=fp)                                           
      message = fp.getvalue()                                                
      logging.info(message)                                                  
                                                                             
      logging.warning('err:%s %s' %(err, sys._getframe().f_back.f_code) ) 


 
  def __tidy_data(self, res ):
    for re in res:
      if not  re.get('imei'):
        res.remove(re)
        continue
      if not str(re.get('imei')).isdigit():
        res.remove(re) 
        continue
      if len(str(re.get('imei'))) != 15:
        res.remove(re)
        continue
      if not ImeiSupport.isValid(re.get('imei')): 
        res.remove(re) 
        continue
  def __fetch_data(self):
    index=0
    cursor = self.dbconn.cursor()
    for re_user in self.__users:
      '''
      if not str(re_user.get('imei')).isdigit():
        continue
      if not ImeiSupport.isValid( re_user.get('imei')):
        continue 
      '''
      for re_imei in self.__imei:
        if re_user.get('imei') == re_imei.get('imei'): 
          f_imei = re_user.get('imei')
          ''' 
          if not str(f_imei).isdigit():
            self.__imei.remove(re_imei)
            continue
          if not  ImeiSupport.isValid( f_imei ):  
            self.__imei.remove(re_imei)
            continue
          '''
          sql = self.__handle_data(re_user, re_imei)
          if sql:
            logging.info("index:%d sql:%s"%(index, sql))
            cursor.execute(sql)
            if index%500 == 0:
              logging.info('index %d db commit'%(index))
              self.dbconn.commit()
            index += 1
    logging.info('end index %d db commit'%(index))
    self.dbconn.commit()
    cursor.close() 
             
           
          
            
  def __handle_data(self, re_user, re_imei):
    if type(re_user) != dict or type(re_imei) != dict:
      return 
    if len(re_user) <1 or len(re_imei) < 1:
      return
    add_dict = {}
   
    #手机型号
    if re_imei.get('device_model') and len(str(re_imei.get('device_model')))>0:
      add_dict['cellphone_model'] = re_imei.get('device_model') 
    #系统类型
    if re_imei.get('device_type') and len(str(re_imei.get('device_type')))>0:
       dev_type =  str(re_imei.get('device_type')).strip()
       if dev_type == '1' or   dev_type =='a':                            
         add_dict['cellphone_os_type'] = '安卓' 
       elif dev_type == '2' or  dev_type == 'i' or  dev_type =='ios':     
         add_dict['cellphone_os_type'] = 'IOS'
    #系统版本 
    if re_imei.get('device_platform') and len(str(re_imei.get('device_platform')))>0:
      add_dict['cellphone_os_version'] = re_imei.get('device_platform')

    if re_imei.get('device_model') and len(str(re_imei.get('device_model')))>0:
      add_dict['cellphone_model'] = re_imei.get('device_model')
   
    #handle the city 
    if re_imei.get('city') and len(str( re_imei.get('city')))>0: 
      self.__handle_city(  re_imei, add_dict ) 

    #handel the address
    if re_imei.get('address') and len(str(re_imei.get('address')))>0:
      self.__handle_address(re_imei, add_dict)
 
    if len(add_dict )>0:
      add_dict['imei'] = re_imei.get('imei')  
      sql = generate_update_sql( add_dict, 'usr_user_basic_info')
      return sql 


  #re  yd_client_device 符合条件的字典
  def __handle_city(self, re, add_dict):
    if type(re) != dict or len(re)<1:
      return
    if not re.get('city'):
      return
    city = str(re.get('city'))
    pos =  city.find('市')
    if pos>0:
      city = city[:pos]
   
    #sele.__city  tg_tags_info 城市里表  
    for re in self.__city:
      if str(re.get('tag_name')).find(city) != -1: 
        add_dict['residential_city'] = re.get('tag_name')                  
      else:
        if self.get_tag_name('residential_city',city):
          add_dict['residential_city'] = self.get_tag_name('residential_city', city ) 

    #find the provice of the city
    for re1 in self.__province_city:
      if  str(re1.get('city')).find(city) != -1:
        province = re1.get('province') 
        if self.get_tag_name('residential_province', province):
          add_dict['residential_province'] = self.get_tag_name('residential_province',\
                                                               province) 
         



  def __handle_address(self, re, add_dict):
    # to find the city in tg_tags_info                                     
    if type(re) != dict or len(re)<1:
      return
    if not re.get('address'):
      return 
    address = str(re.get('address'))
    for district  in self.__district:
      if address.find( district.get('tag_name')) != -1:
        #print(district.get('tag_name')) 
        add_dict['residential_district'] = district.get('tag_name') 
      if address.find( self.get_tag_name('residential_district', \
                                       district.get('tag_name'))) != -1: 
        add_dict['residential_district'] = self.get_tag_name('residential_district',
                                               district.get('tag_name'))  
    for  community in self.__community: 
      if address.find( community.get('tag_name'))!= -1:
        #print(community.get('tag_name')) 
        add_dict['residential_community'] = community.get('tag_name')
        


  #同义词处理, 返回同义词
  def get_tag_name(self, column, value ):
    if column is None or value is None:
      return
    sql = "SELECT b.tag_name,  b.synonym  FROM `tg_tags_class_info` a, tg_tags_info b where \
         a.class_id= b.class_id  and a.rel_table=1 and a.rel_column='%s' "%(column)
    res = self.__select_db(sql,True)
    if not res:
      return
    for row in res:
      if not row.get('tag_name'):
        continue
      if row.get('synonym'):
        if row.get('synonym').find(',') != -1:
          cols = row.get('synonym').split(',')
          for col in cols:
            if col == value:
              return row.get('tag_name')
        else:
          if row.get('synonym') == value:
            return row.get('tag_name')

      if row.get('tag_name') == value:
        return  value

                                                                           
     
	
  #通过select count 查询数据
  def __table_cnt_count(self,  table=None, wh=None ):
    if table == None:
      raise TypeError('tale name is errro')
    sql = "select count(1) from %s "%(table)
    if wh:
      sql +=  'where %s'%( wh)                                               
    
    res = self.__select_db(sql )
    #print( res )                                                            
    return res[0][0]  

  

  def __update_db(self, sql):
    try:
      cursor = self.dbconn.cursor()
      cursor.execute(sql)
      #logging.info(sql)
      #self.dbconn.commit()
      cursor.close()
    except Exception,err:
      logging.warning('err:%s' %(err) )

  def __select_db(self, sql, dictionary=None):
    try:
      if dictionary:
        cursor = self.dbconn.cursor( pymysql.cursors.DictCursor )
      else:
        cursor = self.dbconn.cursor()                                        
     
      self.dbconn.commit()
      cursor.execute(sql )                                                    
      res = cursor.fetchall()                                                
      cursor.close()                                                         
      return res                                                             
    except Exception,err:                                                    
      logging.warning('err:%s' %(err) )  


def main():
   if len(sys.argv)<>3 or sys.argv[1]<>"-f":
     print "Run Like: %s -f %s.conf"%(sys.argv[0][:-3],sys.argv[0][:-3])
     exit(1)
   cfg_file = None
   if sys.argv[2].find('/')>=0:
     cfg_file=sys.argv[2].find('/')
   else:
     cfg_file=os.environ['HOME']+"/cfg/"+sys.argv[2]
   cfg = UniCfg(cfg_file)
   
   if cfg.loglevel == "DEBUG":
     iLogLevel = logging.DEBUG
   elif cfg.loglevel == "INFO":
     iLogLevel = logging.INFO
   elif cfg.loglevel == "WARN" or cfg.loglevel == "WARNING":
     iLogLevel = logging.WARNING
   else:
     iLogLevel = logging.ERROR
   logging.basicConfig( filename=cfg.logfile,
                        format  = "%(asctime)s %(levelname)s[%(process)d]:%(message)s",
                        level   = iLogLevel)
   
   logging.info("......START.........")
   UDB = UniDB(cfg.dbuser,cfg.dbpass,cfg.dbhost,cfg.dbport,cfg.dbname,cfg.dbtype)
   UDB.open()
   
   obj = Handle(UDB,cfg)
   obj.handle_data()
   
   UDB.close()
   logging.info("......EXIT.........")
   logging.shutdown()
   return

if __name__ == "__main__":
  main()
